# -*- coding: latin-1 -*-
import pandas as pd
from sqlalchemy import create_engine
import os
# ???????
engine = create_engine('mysql+pymysql://tester:tester1234@192.168.3.167/gwza231dev')
devicelist = [
'GW4237'
# ,'GW549C'
# ,'GW40A2'
# ,'GW1721'
# ,'GW1D94'
# ,'GW50AD'
# ,'GW558F'
# ,'GW53AB'
# ,'GW50A6'
# ,'GW4807'
# ,'GW4EB3'
# ,'GW559A'
# ,'GW1746'
# ,'GW5120'
# ,'GW41A4'
# ,'GW441F'
# ,'GW54A8'
# ,'GW1DA4'
# ,'GW55B6'
# ,'GW4684'
# ,'GW53C4'
# ,'GW172D'
# ,'GW511B'
# ,'GW1D53'
# ,'GW514A'
# ,'GW4015'
# ,'GW931C'
# ,'GW9378'
# ,'GW1595'
# ,'GW1756'
# ,'GW5347'
# ,'GW55A0'
# ,'GW5595'
# ,'GW45A8'
# ,'GW5384'
# ,'GW422B'
# ,'GW5548'
# ,'GWD18E'
# ,'GW4EB9'
# ,'GW4755'
# ,'GWFBCF'
# ,'GW42C6'
# ,'GW562B'
# ,'GW528B'
# ,'GW5364'
# ,'GW501F'
# ,'GW5534'
# ,'GW4F19'
]
max_column=0
for id in devicelist:
    # ?MySQL????
    #sql_query = 'select update_time,tag_height_now,tag_pa_now from xiaxiatest_his0719_height WHERE device_id = \'{}\''.format(id)  # ?????SQL??
    sql_query = "SELECT update_time FROM xiaxia_tag_pa1211 WHERE device_id=\"{}\"".format(id)  # ??????????
    #sql_query = f'SELECT * FROM xiaxiatest_his WHERE device_id=\'{id}\' AND update_time BETWEEN \'2024-06-07 16:05:00\' AND \'2024-06-07 20:56:00\' ORDER BY device_id,update_time'

    df = pd.read_sql(sql_query, engine)
    #print(df)
    # ??????

    path = './report/pa/output_pa1211.xlsx'
    name = 'time'
    if not os.path.exists(path):
        df.to_excel(path,sheet_name=name, index=False)
    else:
        with pd.ExcelWriter(path, mode='a', if_sheet_exists='overlay') as writer:
            df.to_excel(writer, sheet_name=name, startcol=max_column, index=False)

    max_column += df.shape[1]
    print(max_column)




# ?????Excel??
#df.to_excel('output.xlsx', startcol=max_column,index=False)